﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class Booking
    {
        //exposed attributes of booking
        public int bookID { get; set; }
        public String bookUserAssigned { get; set; }
        public String bookClient { get; set; }
        public String bookCreator { get; set; }
        public DateTime bookStart { get; set; }
        public DateTime bookEnd { get; set; }
        public String bookTitle { get; set; }
        public String bookDesc { get; set; }
        public Boolean bookApproved { get; set; }
        public Boolean bookActive { get; set; }
        public String bookStatus { get; set; }
        public Boolean bookRecurStatus { get; set; }
        public String bookRecurInterval { get; set; }
        public Boolean bookChargeable { get; set; }
        public Decimal bookAmount { get; set; }
        public String bookColour { get; set; }
        public DateTime createdTimeStamp { get; set; }
        
        //Grabs lists of dependencies
        private ResourceRequests resources;
        private RoomRequests rooms;
        private BookingNotes notes;

        //custom getters for lists above
        public ResourceRequests getResources()
        {
            return resources;
        }

        public RoomRequests getRooms()
        {
            return rooms;
        }

        public BookingNotes getNotes()
        {
            return notes;
        }

        //internal class variables/arrays
        protected String[] attribNames;
        protected int numAttribs;
        private bool NewOrOld;
        protected String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public Booking()
        {
            initHardCodedVals();
            NewOrOld = true;    //if true, is new record

            //Initialise with sensible defaults
            bookID = -1;
            bookUserAssigned = "";
            bookClient = "Internal";
            bookCreator = "";
            bookStart = Convert.ToDateTime(System.DateTime.Now.ToString());
            bookEnd = Convert.ToDateTime(System.DateTime.Now.ToString());
            bookTitle = "";
            bookDesc = "";
            bookApproved = false;
            bookActive = true;
            bookStatus = "Pending Approval";
            bookRecurStatus = false;
            bookRecurInterval = "";
            bookChargeable = false;
            bookAmount = 0;
            bookColour = "#FFFFFF";
            createdTimeStamp = Convert.ToDateTime(System.DateTime.Now.ToString());

            //reads in db connection string
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; }  //this will cause error conditions later
        }

        public int Load(int bookingID)
        {
            //Verify bookingID is sensible
            if (bookingID < 0) { return -1; }

            //re-initialise lists
            resources = new ResourceRequests();
            rooms = new RoomRequests();
            notes = new BookingNotes();

            //check for any notes.
            int loadNotes = notes.Populate(bookingID);
            //no need to bail if no results, as we should be checking for this!
            int loadResources = resources.Populate(bookingID);
            int loadRooms = rooms.Populate(bookingID);

            //Build query string
            String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + bookingID;
            int rCheck = 0; //used for error-checking later
            String[] outputVars = new String[numAttribs + 1];
            
            NewOrOld = false;   //importing existing record

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //build query string
            queryFull += queryCondition;
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //initialise output array
            for (int i = 0; i < outputVars.Length; i++)
            {
                outputVars[i] = "";
            }

            //Import values from query into string array
            while (dbReader.Read())
            {
                if (rCheck > 0)
                {
                    return -1;  //if more than one result was returned, run hard & run fast!
                }
                else
                {
                    int i = 0;  //iterator for output string
                    foreach (String att in attribNames)
                    {
                        try { outputVars[i] = dbReader[att].ToString(); }
                        catch { return -1; }
                        i++;
                    }
                }
                rCheck++;   //tracks number of results
            }

            //Verify that at least one result was returned
            if (rCheck < 1) { return -1; };

            //Import values from Db into class attributes
            try { bookID = Convert.ToInt32(outputVars[0]); }
            catch { return -1; }

            bookUserAssigned = outputVars[1];
            bookClient = outputVars[2];
            bookCreator = outputVars[3];

            try { bookStart = DateTime.Parse(outputVars[4]); }
            catch { return -1; }
            try { bookEnd = DateTime.Parse(outputVars[5]); }
            catch { return -1; }

            bookTitle = outputVars[6];
            bookDesc = outputVars[7];

            try { bookApproved = Convert.ToBoolean(outputVars[8]); }
            catch { return -1; }
            try { bookActive = Convert.ToBoolean(outputVars[9]); }
            catch { return -1; }

            bookStatus = outputVars[10];

            try { bookRecurStatus = Convert.ToBoolean(outputVars[11]); }
            catch { return -1; }

            bookRecurInterval = outputVars[12];

            try { bookChargeable = Convert.ToBoolean(outputVars[13]); }
            catch { return -1; }
            try {
                    bookAmount = Convert.ToDecimal(outputVars[14]);
            }
            catch { return -1; }

            bookColour = outputVars[15];

            try { createdTimeStamp = DateTime.Parse(outputVars[16]); }
            catch { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

           NewOrOld = false; //fail-safe
           return 0;
        }

        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " (";
                //the numerical ID increments for us, so we just need to state which fields we're inserting into
                int i = 0;
                foreach (String att in attribNames)
                {
                    if (i > 0 && i < attribNames.Count()-1)
                    {
                        queryFull += att + ", ";
                    }
                    if(i==attribNames.Count()-1)
                    {
                        queryFull += att + ") ";
                    }
                    
                    i++;
                }
                queryFull += "VALUES (";
                queryFull += "'" + bookUserAssigned + "', ";
                queryFull += "'" + bookClient + "', ";
                queryFull += "'" + bookCreator + "', ";
                queryFull += "'" + bookStart.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += "'" + bookEnd.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += "'" + bookTitle + "', ";
                queryFull += "'" + bookDesc + "', ";

                queryFull += "'" + Convert.ToString(bookApproved) + "', ";
                queryFull += "'" + Convert.ToString(bookActive) + "', ";
                queryFull += "'" + bookStatus + "', ";
                queryFull += "'" + Convert.ToString(bookRecurStatus) + "', ";
                queryFull += "'" + bookRecurInterval + "', ";
                queryFull += "'" + Convert.ToString(bookChargeable) + "', ";

                queryFull += bookAmount + ", ";
                queryFull += "'" + bookColour + "', ";
                queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
            }
            else
            {
                //verify the record actually exists
                Booking test = new Booking();
                int initCheck = test.Load(bookID);
                if (initCheck < 0) { return -1; }

                //build the UPDATE query (clientName is skipped because it's a primary key)
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "=" + bookID;
                queryFull += attribNames[1] + "='" + bookUserAssigned + "', ";  //Note: Check this
                queryFull += attribNames[2] + "='" + bookClient + "', ";
                queryFull += attribNames[3] + "='" + bookCreator + "', ";
                queryFull += attribNames[4] + "='" + bookStart.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += attribNames[5] + "='" + bookEnd.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += attribNames[6] + "='" + bookTitle + "', ";
                queryFull += attribNames[7] + "='" + bookDesc + "', ";
                
                queryFull += attribNames[8] + "='" + Convert.ToString(bookApproved) + "', ";
                queryFull += attribNames[9] + "='" + Convert.ToString(bookActive) + "', ";
                queryFull += attribNames[10] + "='" + bookStatus + "', ";
                queryFull += attribNames[11] + "='" + Convert.ToString(bookRecurStatus) + "', ";
                queryFull += attribNames[12] + "='" + bookRecurInterval + "', ";
                queryFull += attribNames[13] + "='" + Convert.ToString(bookChargeable) + "', ";
                queryFull += attribNames[14] + "=" + bookAmount + ", ";
                queryFull += attribNames[15] + "='" + bookColour + "', ";
                
                queryFull += attribNames[16] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy HH:mm:ss") + "' ";
                queryFull += queryCondition;
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            NewOrOld = false;   //fail-safe
            
            return 0;
        }

        public int Delete()
        {
            //Bookings are never deleted, as this could leave orphaned room/resource usage
            //and would compromise statistics/invoicing. Made inactive instead.
            if (bookID < 0) { return -1; }

            bookActive = false;
            int makeInactive = Save();
            if (makeInactive < 0) { return -1; }
            return 0;
        }

        protected void initHardCodedVals()
        {
            //set hard-coded values
            numAttribs = 16;
            tableName = "tblBookings";
            attribNames = new String[numAttribs + 1];
            attribNames[0] = "intBookingID"; //primary key value
            attribNames[1] = "txtAssignedUser";
            attribNames[2] = "txtClientName";
            attribNames[3] = "txtUserName";
            attribNames[4] = "dtBookingStart";
            attribNames[5] = "dtBookingEnd";
            attribNames[6] = "txtTitle";
            attribNames[7] = "txtDescription";
            attribNames[8] = "bitApproved";
            attribNames[9] = "bitActive";
            attribNames[10] = "txtStatus";
            attribNames[11] = "bitRecurring";
            attribNames[12] = "txtRecurInterval";
            attribNames[13] = "bitChargeable";
            attribNames[14] = "numAmount";
            attribNames[15] = "txtColour";
            attribNames[16] = "dtTimeStamp";
        }
    }
}